﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;

namespace TestJQGrid
{
    public partial class ViewReports : System.Web.UI.Page
    {
        #region init - load/unload    
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void LBReportList_DataBound(object sender, EventArgs e)
        {
            loadReport2Grid();
        }

        protected void LBReportList_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadReport2Grid();
        }

        private void loadReport2Grid()
        {
            //get report id
            if (LBReportList.SelectedIndex == -1)
                LBReportList.SelectedIndex = 0;
            int reportId = Convert.ToInt32(LBReportList.SelectedValue);

            //get data
            string query = "SELECT [SqlQuery] FROM [Reports] WHERE [ReportId] = '" + reportId.ToString() + "'";
            DataTable reportData = GlobalMethods.ExecuteSelectQuery(GlobalMethods.ExecuteScalar(query));
            ViewState["ReportData"] = reportData;

            //display data
            GVReport.DataSource = reportData;
            GVReport.DataBind();

            return;
        }
        #endregion

        #region download file as pdf/excel/csv/ms word/text
        protected void BDownload_Click(object sender, EventArgs e)
        {
            DataTable tempDt = (DataTable)ViewState["ReportData"];

            if (DDLDownloadType.SelectedItem.Text == "PDF")
                DownloadAsPDF(tempDt);
            else if (DDLDownloadType.SelectedItem.Text == "Excel")
                DownloadAsExcel(tempDt);
            else if (DDLDownloadType.SelectedItem.Text == "CSV (,)")
                DownloadAsCSV(tempDt, ',');
            else if (DDLDownloadType.SelectedItem.Text == "CSV (|)")
                DownloadAsTXT(tempDt, '|');
            else if (DDLDownloadType.SelectedItem.Text == "MS WORD")
                DownloadAsWORD(tempDt);

            return;
        }

        private void DownloadAsWORD(DataTable dt2Download)
        {
            //Create a dummy GridView and attach data
            GridView dummyGrid = new GridView();
            dummyGrid.AllowPaging = false;
            dummyGrid.DataSource = dt2Download;
            dummyGrid.DataBind();

            //create response
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=Report.doc");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-word ";

            //write to file
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            dummyGrid.RenderControl(hw);
            Response.Output.Write(sw.ToString());

            //finish
            Response.Flush();
            Response.End();
        }

        private void DownloadAsPDF(DataTable dt2Download)
        {
            //Create a dummy GridView
            GridView dummyGrid = new GridView();
            dummyGrid.AllowPaging = false;
            dummyGrid.DataSource = dt2Download;
            dummyGrid.DataBind();

            //create response
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-disposition", "attachment;filename=Report.pdf");
            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            //create controls
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            dummyGrid.RenderControl(hw);
            StringReader sr = new StringReader(sw.ToString());
            Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
            HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
            PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

            //write to pdf
            pdfDoc.Open();
            htmlparser.Parse(sr);
            pdfDoc.Close();
            Response.Write(pdfDoc);
            Response.End();
        }

        private void DownloadAsExcel(DataTable dt2Download)
        {
            //Create a dummy GridView
            GridView dummyGrid = new GridView();
            dummyGrid.AllowPaging = false;
            dummyGrid.DataSource = dt2Download;
            dummyGrid.DataBind();

            //create response
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=Report.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            //.xlsx = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

            //write to file
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            //formatting
            for (int i = 0; i < dummyGrid.Rows.Count; i++)
            {
                //Apply text style to each Row
                dummyGrid.Rows[i].Attributes.Add("class", "textmode");
            }
            dummyGrid.RenderControl(hw);
            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";

            //save the file
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

        private void DownloadAsCSV(DataTable dt2Download, char seperator)
        {
            //create response
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=Report.csv");
            Response.Charset = "";
            Response.ContentType = "application/text";

            //append seperator and new line
            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < dt2Download.Columns.Count; k++)
                sb.Append(dt2Download.Columns[k].ColumnName + seperator);
            sb.Append("\r\n");

            //do it for all rows
            for (int i = 0; i < dt2Download.Rows.Count; i++)
            {
                for (int k = 0; k < dt2Download.Columns.Count; k++)
                    sb.Append(dt2Download.Rows[i][k].ToString().Replace(",", ";") + seperator);
                sb.Append("\r\n");
            }

            //write to file
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }

        private void DownloadAsTXT(DataTable dt2Download, char seperator)
        {
            //create response
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=Report.txt");
            Response.Charset = "";
            Response.ContentType = "application/text";

            //append seperator and new line
            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < dt2Download.Columns.Count; k++)
                sb.Append(dt2Download.Columns[k].ColumnName + seperator);
            sb.Append("\r\n");

            //do it for all rows
            for (int i = 0; i < dt2Download.Rows.Count; i++)
            {
                for (int k = 0; k < dt2Download.Columns.Count; k++)
                    sb.Append(dt2Download.Rows[i][k].ToString().Replace(",", ";") + seperator);
                sb.Append("\r\n");
            }

            //write to file
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }
        #endregion
    }
}